import pytest
import os
from common.Yaml import Yaml
from common.Request import Request
from common.Path import project_path
from common.connect_mysql import Connect_Mysql
filepath=project_path()
import allure
#读取common.yaml文件,headers,base_url等信息
common_yaml_file=os.path.join(filepath,"common", "common.yaml")
common_yaml_data = Yaml(common_yaml_file).read_yaml()
headers=common_yaml_data['headers']
base_url=common_yaml_data['base_url']
req=Request(base_url)
import pandas as pd
'''
sql="""select inventory_sku,spu from df_test_database.df_goods_spec dgs 
inner join df_test_database.df_goods_information dgi on dgs.spu_id =dgi.spu_id 
where sku_id not in(select sku_id from df_test_database.df_goods_supplier dgs WHERE supplier_id=' 1927647198528995328') and invented_sku is not NULL and spu is not NULL"""

cm = Connect_Mysql('df_test_database')
result = cm.select_mysql(sql, None)
data_list=list(result)




url="/api/purchase/goodsSuppliers/connectGoodsSupplier.json"

 #供应商管理关联商品
def add_connectGoodsSupplier(spu,inventorySku):
    json_data = {"spu": spu, "supplierId": "1927647198528995328", "inventorySku": inventorySku}
    response=req.send_request("POST",url=base_url+url,json=json_data,headers=heads).json()
    return response
for item in data_list:
    print(add_connectGoodsSupplier(item[1],item[0]))
'''
#商品资料添加供应商
'''
sql="""select supplier_id,supplier_name,supplier_link from df_test_database.df_suppliers ds where supplier_id not in
(select supplier_id from df_test_database.df_goods_supplier dgs where sku_id ='710058317724127233' ) and del_flag=0"""
cm = Connect_Mysql('df_test_database')
result = cm.select_mysql(sql, None)
data_list=list(result)
list_supplier=[]

print("data_list",data_list)
for item in data_list:

    dict_supplier = {
        "supplierId": item[0],
        "supplierName": item[1],
        "supplierLink": item[2],
        "purchaseLink": "",
        "lastPurchasePrice": "",
        "minPurchasePrice": ""
    }
    list_supplier.append(dict_supplier)
print(list_supplier)

json_data={
	"spu": "XJ02516130",
	"originalFactorySku": "",
	"inventedSku": "",
	"buyer": "",
	"salesPeople": "",
	"ui": "往前342432",
	"createBy": "426",
	"categoryId": "1294",
	"categoryFullId": "17-144-1294",
	"productNameZh": "可爱苹果公仔挂件少女心包包挂件百搭毛绒玩偶钥匙扣",
	"productNameEn": "可爱苹果公仔挂件少女心包包挂件百搭毛绒玩偶钥匙扣Nick",
	"proTagZh": [],
	"proTagEn": ["可爱", "公仔", "苹果", "挂件", "少女心", "包包挂件", "百搭玩偶"],
	"status": "1",
	"salesUnit": "1个",
	"isTort": "0",
	"isVideo": "0",
	"videoLink": "",
	"attributeList": [],
	"declareNameZh": "钥匙扣",
	"declareNameEn": "Key Chain",
	"declarPrice": "1",
	"customsCode": "",
	"brand": "",
	"origin": "",
	"productMaterial": "",
	"productUse": "",
	"isBattery": "0",
	"isMagnetic": "0",
	"isLiquid": "0",
	"isPowder": "0",
	"isPasta": "0",
	"isCutter": "0",
	"isCombustible": "0",
	"isCorrosion": "0",
	"inventoryImgLink": "https://img.dengfengkeji.cn/dev/product/20250514/121851/D9eteN/cj4114355_red-a.jpg",
	"windowPicList": "",
	"sizeImgList": "https://img.dengfengkeji.cn/dev/product/20250514/121900/SYH7Kl/d.jpg",
	"describeHtml": "",
	"describeText": "可爱/苹果/公仔/挂件少女/心包包挂/件百搭毛绒玩/偶钥匙扣\n可爱/苹果/公仔/挂件少女/心包包挂/件百搭毛绒玩/偶钥匙扣\n可爱/苹果/公仔/挂件少女/心包包挂/件百搭毛绒玩/偶钥匙扣\n可爱/苹果/公仔/挂件少女/心包包挂/件百搭毛绒玩/偶钥匙扣",
	"minPurchasePrice": "",
	"lastPurchasePrice": "",
	"referenceLink": "https://detail.1688.com/offer/902795763096.html",
	"purchaseRemark": "采购价：\n1#一口苹果-公仔挂件  1.7\n2#半个苹果-公仔挂件  2.2\n3#吃完苹果-公仔挂件  1.8\n含包装  包邮   采购量各20 采购价：\n1#一口苹果-公仔挂件  1.7\n2#半个苹果-公仔挂件  2.2\n3#吃完苹果-公仔挂件  1.8\n含包装  包邮   采购量各20",
	"productRemark": "PHS1；PHF1;IDS1;IDF1 PHS1；PHF1;IDS1;IDF1",
	"sellRemark": "",
	"selectedSupplierId": "",
	"supplierList": list_supplier,
	"relevanceProject": "CN6602-南宁边境仓-发货仓",
	"trackOrderBy": "",
	"onPassageNum": "",
	"certifications": [],
	"spuId": "709693688044982282",
	"skuId": "1928369886934962176",
	"isSingleVariant": "false",
	"attributeName": "[{\"name\":\"颜色\",\"value\":\"黑色\"}]",
	"inventorySku": "XJ02516130_黑色",
	"unitPrice": "",
	"packingPrice": "",
	"labellingPrice": "",
	"freightApportion": "",
	"purchasePrice": "",
	"storePrice": "",
	"discountPrice": "",
	"weight": "",
	"length": "",
	"width": "",
	"height": "",
	"packageLength": "3.9",
	"packageWidth": "3.89",
	"packageHeight": "9.67",
	"volume": "",
	"packageVolume": "",
	"weightAfterPackage": "",
	"packingQuantity": "",
	"packagingMaterials": "",
	"specifications": "",
	"liveness": "",
	"stopSellingTime": "2025-05-16 08:00:00",
	"volumeWeightCoefficient": "",
	"cartonSize": "",
	"attributedPicList": "",
	"warehouseList": [{
		"warehouseId": "119",
		"price": "666",
		"transportType": "",
		"stockNumber": "",
		"inventoryNum": ""
	}]
}
def editSku(json_data):
    url="/api/gds/inf/v2/editSku.json"
    response=req.send_request("POST",url=base_url+url,json=json_data,headers=heads).json()
    return response
print(editSku(json_data))
'''
sql="""select inventory_sku,dgi.spu from df_goods_spec dgs 
inner join df_goods_information dgi on dgs.spu_id =dgi.spu_id
where sku_id not in(select sku_id  from df_goods_supplier dgs2) and invented_sku is not null and dgi.spu is not null"""
cm = Connect_Mysql('df_test_database')
result = cm.select_mysql(sql, None)
data_list=list(result)
print(data_list)

data_list = [{'inventory_sku': row[0], 'spu': row[1]} for row in result]

# 初始化包含其他字段的字典列表
data_dicts = [
    {
        "*SKU": item['inventory_sku'],
        "*SPU": item['spu'],
        "*供应商名称": '供应商0528新增1999'  # 写死的供应商名称
    }
    for item in data_list
]

# 创建 DataFrame
df = pd.DataFrame(data_dicts)

# 写入 Excel 文件
df.to_excel('spu_supplier.xlsx', index=False, engine='openpyxl')